package com.skycode7.dao;
import com.skycode7.bean.NoticeBean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import com.skycode7.util.DBConnection;

public class NoticeDao {
    DBConnection db = new DBConnection();
    private List<NoticeBean> list;

    public int add(NoticeBean bean) {
        Connection conn = db.getConn();
        String sql = "insert into notice  VALUES (?,?,?,?)";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, bean.getNoticeTitle());
            ps.setString(2, bean.getNoticeContent());
            ps.setString(3, bean.getNoticeTime());
            ps.setString(4, bean.getAdminName());
            int count = ps.executeUpdate();
            conn.close();
            return count;
        } catch (SQLException e) {
            System.out.println("插入notice表异常");
        }
        return 0;
    }

    /**
     *
     */
    public int delect(int NoticeID) {
        Connection conn = db.getConn();
        String sql = "delete from notice where NoticeID =?";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, NoticeID);
            return ps.executeUpdate();
        } catch (SQLException e) {
            System.out.println("删除notice表异常");
        }

        return 0;
    }

    public int update(NoticeBean notice) {
        Connection conn = db.getConn();
        String sql = "update notice set NoticeTitle =?, NoticeContent =?, NoticeTime =? ,AdminName = ? where NoticeID =?";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, notice.getNoticeTitle());
            ps.setString(2, notice.getNoticeContent());
            ps.setString(3, notice.getNoticeTime());
            ps.setString(4, notice.getAdminName());
            ps.setInt(5, notice.getNoticeID());
            return ps.executeUpdate();
        } catch (SQLException e) {
            System.out.println("插入notice表异常");
        }
        return 0;
    }

    /**
     *
     */
    public List<NoticeBean> getList(int page) {
        Connection conn = db.getConn();
        String sql = "select * from notice limit ?,?";
        List<NoticeBean> List = new java.util.ArrayList<NoticeBean>();
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, (page - 1) * 10);
            ps.setInt(2, 10);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                NoticeBean notice = new NoticeBean();
                notice.setNoticeID(rs.getInt("NoticeID"));
                notice.setNoticeTitle(rs.getString("NoticeTitle"));
                notice.setNoticeContent(rs.getString("NoticeContent"));
                notice.setNoticeTime(rs.getString("NoticeTime"));
                notice.setAdminName(rs.getString("AdminName"));
                List.add(notice);
            }
        } catch (SQLException e) {
            System.out.println("查询notice表异常");
        }
        return list;
    }
}




